﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using UtilityClassLIB;

namespace GroupsClassLIB
{
    public class GroupsClass
    {
        UtilityClass utility_obj = new UtilityClass();//creates utility class object
        String connectionString = UtilityClass.ConnectionString();//gets connection string from utility class


        public string InsertRowinGroupTable(string gn, string gp, string gd)
        {
            string result = "";
            DataTable dt = utility_obj.fillDataTable("tbGroups");
            DataRow[] dr = dt.Select();
            int uid = 0;

            //creates unique user id by finding highest user id and adding 1
            foreach (DataRow row in dt.Rows)
            {
                uid = row.Field<int>("groupID");
                int maxUID = Math.Max(Int32.MaxValue, uid);
            }
            uid = uid + 1;

            //inserts row into user table,
            SqlConnection connection = new SqlConnection(connectionString);

            try
            {
                using (SqlCommand insertCommand = new SqlCommand(
                "INSERT INTO dbo.tbGroups VALUES(@groupID, @groupName,@groupPriority, @groupDescription)", connection))
                {
                    insertCommand.Parameters.AddWithValue("@groupID", uid);
                    insertCommand.Parameters.AddWithValue("@groupName", gn);
                    insertCommand.Parameters.AddWithValue("@groupPriority", gp);
                    insertCommand.Parameters.AddWithValue("@groupDescription", gd);

                    insertCommand.Connection = connection;
                    connection.Open();
                    insertCommand.ExecuteNonQuery();
                    result = "Success! Group Added: " + Convert.ToString(uid) + " " + gn + " " + gp + " " + gd;
                }

            }//end try
            catch (System.Data.SqlClient.SqlException sqlException)
            {
                result = sqlException.Message;
            }

            return result;
        }// insert row in group table


        public string UpdateGroup(string gid, string gn, string gp, string gd)
        {
            string result = "";

            SqlConnection connection = new SqlConnection(connectionString);

            try
            {
                using (SqlCommand insertCommand = new SqlCommand(
                "UPDATE dbo.tbGroups SET  groupName=@groupName, groupPriority = @groupPriority, groupDescription = @groupDescription  where groupID ='" + gid + "'", connection))
                {
                    insertCommand.Parameters.AddWithValue("@groupID", gid);
                    insertCommand.Parameters.AddWithValue("@groupName", gn);
                    insertCommand.Parameters.AddWithValue("@groupPriority", gp);
                    insertCommand.Parameters.AddWithValue("@groupDescription", gd);

                    insertCommand.Connection = connection;
                    connection.Open();
                    insertCommand.ExecuteNonQuery();
                    result = "Success! Group Updated: " + gid + " " + gn + " " + gp + " " + gd;
                }
            }//end try
            catch (System.Data.SqlClient.SqlException sqlException)
            {
                result = sqlException.Message;
            }

            return result;
        }//end update group

        //---------------------------------just in case we need it
        public string DeleteGroup(string gid, string gn, string gp, string gd)
        {
            string result = "";

            SqlConnection connection = new SqlConnection(connectionString);

            try
            {
                using (SqlCommand deleteCommand = new SqlCommand(
                "DELETE FROM dbo.tbGroups WHERE groupID ='" + gid + "'", connection))
                {

                    deleteCommand.Connection = connection;
                    connection.Open();
                    deleteCommand.ExecuteNonQuery();
                    result = "This Group has been deleted: " + gid + " " + gn + " " + gp + " " + gd;
                }
            }//end try
            catch (System.Data.SqlClient.SqlException sqlException)
            {
                result = sqlException.Message;
            }
            return result;
        }

    }//end class
}//end namespace
